﻿using System;
using System.Collections.Generic;

using System.Text;
using System.Data.OleDb;
using TdgMaker.DataType;
using System.Collections;

namespace TdgMaker
{
    class TB_DB
    {//Build our own connection string
        private static OleDbConnection GetConnection()
        {
            OleDbConnection conn = new OleDbConnection();
            string loc = System.AppDomain.CurrentDomain.SetupInformation.ApplicationBase + "TB.mdb";
            conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + loc + ";";
            conn.Open();
            return conn;
        }
        static OleDbConnection conn = null;
        public static void GetConn()
        {
            try
            {
                conn = GetConnection();
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); }
        }

        public static void DisConn()
        {
            try
            {
                conn.Dispose();
                conn = new OleDbConnection();
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); conn = new OleDbConnection(); }
        }
        static int commandCount = 0;

        private static void CheckCount()
        {
            commandCount++;
            if (commandCount > 300)
            {
                DisConn();
                GetConn();
                commandCount = 0;
            }
        }
        public static bool clearAll()
        {
            try
            {
                CheckCount();
                OleDbCommand command = conn.CreateCommand();
                command.CommandText = "DELETE FROM BaliseTele";
                command.ExecuteNonQuery();
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return false; }
            try
            {
                CheckCount();
                OleDbCommand command = conn.CreateCommand();
                command.CommandText = "DELETE FROM TrackDataDownLine";
                command.ExecuteNonQuery();
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return false; }
            try
            {
                CheckCount();
                OleDbCommand command = conn.CreateCommand();
                command.CommandText = "DELETE FROM TrackDataUpLine";
                command.ExecuteNonQuery();
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return false; }
            try
            {
                CheckCount();
                OleDbCommand command = conn.CreateCommand();
                command.CommandText = "DELETE FROM BalisePos";
                command.ExecuteNonQuery();
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return false; }
            try
            {
                CheckCount();
                OleDbCommand command = conn.CreateCommand();
                command.CommandText = "DELETE FROM Route";
                command.ExecuteNonQuery();
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return false; }
            return true;
        }
        public static bool adNewBaliseTele(BaliseTele baliseTele)
        {
            try
            {
                CheckCount();
                OleDbCommand command = conn.CreateCommand();
                command.CommandText = string.Format
                    (
                    "INSERT INTO BaliseTele(Balise_Nr,Tele_Version,Tele) VALUES ({0},'{1}','{2}')",
                    baliseTele.Balise_Nr,
                    baliseTele.Tele_Version,
                    baliseTele.Tele
                    );
                OleDbDataReader myReader = command.ExecuteReader();
                return true;
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return false; }
        }

        public static bool adNewTrackDataDownLine(TrackData trackData)
        {
            try
            {
                CheckCount();
                OleDbCommand command = conn.CreateCommand();
                command.CommandText = string.Format
                    (
                    "INSERT INTO TrackDataDownLine(SNameTName,TrackLoc,Type,Length,CF,Signal_Name) VALUES ('{0}',{1},'{2}',{3},'{4}','{5}')",
                    trackData.SNameTName,
                    trackData.TrackLoc,
                    trackData.Type,
                    trackData.Length,
                    trackData.CF,
                    trackData.SignalName
                    );
                OleDbDataReader myReader = command.ExecuteReader();
                return true;
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return false; }
        }

        public static bool adNewTrackDataUpLine(TrackData trackData)
        {
            try
            {
                CheckCount();
                OleDbCommand command = conn.CreateCommand();
                command.CommandText = string.Format
                    (
                    "INSERT INTO TrackDataUpLine(SNameTName,TrackLoc,Type,Length,CF,Signal_Name) VALUES ('{0}',{1},'{2}',{3},'{4}','{5}')",
                    trackData.SNameTName,
                    trackData.TrackLoc,
                    trackData.Type,
                    trackData.Length,
                    trackData.CF,
                    trackData.SignalName
                    );
                OleDbDataReader myReader = command.ExecuteReader();
                return true;
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return false; }
        }

        public static bool adNewBalisePos(BalisePos balisePos)
        {
            try
            {
                CheckCount();
                OleDbCommand command = conn.CreateCommand();
                command.CommandText = string.Format
                    (
                    "INSERT INTO BalisePos(Balise_Nr,Balise_Type,Balise_Loc,SNameTName,Track_Offset,Up_Down,Balise_Name,Lateral_Line,NIDBG) VALUES ({0},'{1}',{2},'{3}',{4},{5},'{6}','{7}','{8}')",
                    balisePos.Balise_Nr,
                    balisePos.Balise_Type,
                    balisePos.Balise_Loc,
                    balisePos.SNameTName,
                    balisePos.Track_Offset,
                    balisePos.Up_Down,
                    balisePos.Balise_Name,
                    balisePos.Lateral_Line,
                    balisePos.getNID_BG()
                    );
                OleDbDataReader myReader = command.ExecuteReader();
                return true;
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return false; }
        }

        public static ArrayList GetTrackListForBalisePos(int UpDown)
        {
            try
            {
                CheckCount();
                ArrayList temp = new ArrayList();
                string strCom = "";
                if (UpDown == 0)
                {
                    strCom = " SELECT SNameTName,TrackLoc,Length,Signal_Name FROM TrackDataUpLine order by TrackLoc DESC";
                }
                else
                {
                    strCom = " SELECT SNameTName,TrackLoc,Length,Signal_Name FROM TrackDataDownLine order by TrackLoc ASC";
                }
                OleDbCommand myOleDbCommand = new OleDbCommand(strCom, conn);
                OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
                while (myDataReader.Read())
                {
                    TrackData td = new TrackData();
                    td.SNameTName = Convert.ToString(myDataReader.GetValue(0));
                    td.TrackLoc = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(1)), 10);
                    td.Length = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(2)), 10);
                    td.SignalName = Convert.ToString(myDataReader.GetValue(3));
                    temp.Add(td);
                }
                return temp;
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return null; }
        }

        public static ArrayList GetBaliseTele(int baliseNr)
        {
            try
            {
                CheckCount();
                ArrayList temp = new ArrayList();
                string strCom = "";

                strCom = " SELECT * FROM BaliseTele WHERE Balise_Nr = \"" + baliseNr.ToString() + "\"";

                OleDbCommand myOleDbCommand = new OleDbCommand(strCom, conn);
                OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
                while (myDataReader.Read())
                {
                    BaliseTele bt = new BaliseTele();
                    bt.Balise_Nr = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(1)), 10);
                    bt.Tele_Version = Convert.ToString(myDataReader.GetValue(2));
                    bt.Tele = Convert.ToString(myDataReader.GetValue(3));
                    temp.Add(bt);
                }
                return temp;
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return null; }
        }

        public static ArrayList GetRoute()
        {
            try
            {
                CheckCount();
                ArrayList temp = new ArrayList();
                string strCom = "";

                strCom = " SELECT * FROM Route";

                OleDbCommand myOleDbCommand = new OleDbCommand(strCom, conn);
                OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
                while (myDataReader.Read())
                {
                    RouteData rt = new RouteData();
                    rt.SName_RouteNr = Convert.ToString(myDataReader.GetValue(0));
                    rt.IL_Nr = Convert.ToString(myDataReader.GetValue(1));
                    rt.RouteType = Convert.ToString(myDataReader.GetValue(2));
                    rt.BaliseNr = Convert.ToString(myDataReader.GetValue(3));
                    rt.PassBalise = Convert.ToString(myDataReader.GetValue(4));
                    rt.PassSwith = Convert.ToString(myDataReader.GetValue(5));
                    rt.Speeds = Convert.ToString(myDataReader.GetValue(6));
                    rt.PassTracks = Convert.ToString(myDataReader.GetValue(7));
                    rt.sSignal = Convert.ToString(myDataReader.GetValue(8));
                    rt.eSignal = Convert.ToString(myDataReader.GetValue(9));

                    rt.analysisTrack();
                    temp.Add(rt);
                }
                return temp;
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return null; }
        }

        public static void GetBaliseList(string SNameTName, ArrayList baliseList)
        {
            try
            {
                CheckCount();
                string strCom = "";

                strCom = " SELECT * FROM BalisePos WHERE SNameTName = \"" + SNameTName + "\"";

                OleDbCommand myOleDbCommand = new OleDbCommand(strCom, conn);
                OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
                while (myDataReader.Read())
                {
                    BalisePos balisePos = new BalisePos();
                    balisePos.Balise_Nr = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(0)), 10);
                    balisePos.Balise_Type = Convert.ToString(myDataReader.GetValue(1));
                    balisePos.Balise_Loc = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(2)), 10);
                    balisePos.SNameTName = Convert.ToString(myDataReader.GetValue(3));
                    balisePos.Track_Offset = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(4)), 10);
                    balisePos.Up_Down = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(5)), 10);
                    balisePos.Balise_Name = Convert.ToString(myDataReader.GetValue(6));

                    baliseList.Add(balisePos);
                }
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); }
        }



       

        public static ArrayList GetTrackListByPos(int UpDown, int StartLoc, int EndLoc)
        {
            try
            {
                CheckCount();
                bool isNotReverse = false;
                if (StartLoc < EndLoc)
                {
                    if (UpDown == 1)
                    {
                        isNotReverse = true;
                    }
                    else
                    {
                        isNotReverse = false;
                    }
                }
                else
                {
                    if (UpDown == 1)
                    {
                        isNotReverse = false;
                    }
                    else
                    {
                        isNotReverse = true;
                    }
                }
                ArrayList temp = new ArrayList();
                string strCom = "";
                if (UpDown == 0)
                {
                    if (StartLoc > EndLoc)
                    {
                        strCom = " SELECT SNameTName,TrackLoc,Length,CF,Type,Signal_Name FROM TrackDataUpLine WHERE (TrackDataUpLine.TrackLoc - TrackDataUpLine.Length) <= \"" + StartLoc + "\" AND TrackDataUpLine.TrackLoc >= \"" + EndLoc + "\" order by TrackLoc DESC";
                    }
                    else
                    {
                        strCom = " SELECT SNameTName,TrackLoc,Length,CF,Type,Signal_Name FROM TrackDataUpLine WHERE TrackDataUpLine.TrackLoc >= \"" + StartLoc + "\" AND (TrackDataUpLine.TrackLoc - TrackDataUpLine.Length) <= \"" + EndLoc + "\" order by TrackLoc DESC";
                    }
                }
                else
                {
                    if (StartLoc > EndLoc)
                    {
                        strCom = " SELECT SNameTName,TrackLoc,Length,CF,Type,Signal_Name FROM TrackDataDownLine WHERE TrackDataDownLine.TrackLoc <=\"" + StartLoc + "\" AND (TrackDataDownLine.TrackLoc + TrackDataDownLine.Length) >= \"" + EndLoc + "\" order by TrackLoc ASC";
                    }
                    else
                    {
                        strCom = " SELECT SNameTName,TrackLoc,Length,CF,Type,Signal_Name FROM TrackDataDownLine WHERE (TrackDataDownLine.TrackLoc + TrackDataDownLine.Length) >= \"" + StartLoc + "\" AND TrackDataDownLine.TrackLoc <= \"" + EndLoc + "\" order by TrackLoc ASC";
                    }
                }
                OleDbCommand myOleDbCommand = new OleDbCommand(strCom, conn);
                OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
                while (myDataReader.Read())
                {
                    TrackData td = new TrackData();
                    td.SNameTName = Convert.ToString(myDataReader.GetValue(0));
                    td.TrackLoc = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(1)), 10);
                    td.Length = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(2)), 10);
                    td.CF = Convert.ToString(myDataReader.GetValue(3));
                    td.Type = Convert.ToString(myDataReader.GetValue(4));
                    td.SignalName = Convert.ToString(myDataReader.GetValue(5));
                    td.isNotReverse = isNotReverse;
                    td.isDown = Convert.ToBoolean(UpDown);
                    temp.Add(td);
                }
                return temp;
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return null; }
        }

        internal static bool adNewRoute(RouteData rd)
        {
            try
            {
                CheckCount();
                OleDbCommand command = conn.CreateCommand();
                command.CommandText = string.Format
                    (
                    "INSERT INTO Route(SName_RouteNr,IL_Nr,RouteType,BaliseNr,PassBalise,PassSwith,Speeds,PassTracks,StartSignal,EndSignal) VALUES ('{0}','{1}','{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}')",
                    rd.SName_RouteNr,
                    rd.IL_Nr,
                    rd.RouteType,
                    rd.BaliseNr,
                    rd.PassBalise,
                    rd.PassSwith,
                    rd.Speeds,
                    rd.PassTracks,
                    rd.sSignal,
                    rd.eSignal
                    );
                OleDbDataReader myReader = command.ExecuteReader();
                return true;
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return false; }
        }

        internal static BalisePos[] getBaliseGroupFromDB(int baliseNIDBG)
        {
            try
            {
                CheckCount();
                string strCom = "";

                strCom = " SELECT * FROM BalisePos WHERE NIDBG = \"" + baliseNIDBG.ToString() + "\"";
                ArrayList baliseList = new ArrayList();
                OleDbCommand myOleDbCommand = new OleDbCommand(strCom, conn);
                OleDbDataReader myDataReader = myOleDbCommand.ExecuteReader();
                while (myDataReader.Read())
                {
                    BalisePos balisePos = new BalisePos();
                    balisePos.Balise_Nr = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(0)), 10);
                    balisePos.Balise_Type = Convert.ToString(myDataReader.GetValue(1));
                    balisePos.Balise_Loc = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(2)), 10);
                    balisePos.SNameTName = Convert.ToString(myDataReader.GetValue(3));
                    balisePos.Track_Offset = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(4)), 10);
                    balisePos.Up_Down = Convert.ToInt32(Convert.ToString(myDataReader.GetValue(5)), 10);
                    balisePos.Balise_Name = Convert.ToString(myDataReader.GetValue(6));

                    baliseList.Add(balisePos);
                }
                BalisePos[] list = new BalisePos[baliseList.Count];
                for (int i = 0; i < list.Length; i++)
                {
                    list[i] = (BalisePos)baliseList[i];
                }
                return list;
            }
            catch (Exception ex) { System.Console.WriteLine(ex.ToString()); return null; }
        }
    }
}
